package com.knife.easyexcel.write.handler;

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.knife.common.utils.KnifeSpringUtil;
import com.knife.easyexcel.write.annotation.BigSpinnerData;
import com.knife.easyexcel.write.supplier.EasyExcelBigSpinnerDataSupplier;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import java.lang.reflect.Field;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 针对字符串过长，超过255个时的处理
 * <p>
 * 添加下拉框，参考https://www.cnblogs.com/konglxblog/p/16456791.html
 *
 * @author： 76875
 * @date： 2022/5/25 星期三 23:34
 * @description：
 * @modifiedBy：
 * @version: 1.0
 */
@SuppressWarnings("ALL")
public class BigSpinnerWriteHandler implements SheetWriteHandler {

    /**
     * 导出的实体模板类型
     */
    private final Class<?> templateClass;
    /**
     * 提供数据的查询类型，一个sheet一个类型
     */
    private final Map<String, Object> sheetSpinnerSupplierParam;

    private String dictSheetName = "字典sheet";

    private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
            'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};


    public BigSpinnerWriteHandler(Class<?> templateClass, Map<String, Object> sheetSpinnerSupplierParam) {
        this.templateClass = templateClass;
        this.sheetSpinnerSupplierParam = sheetSpinnerSupplierParam;
    }

    private String[] readyBigSpinnerData(BigSpinnerData annotation, Map<String, Object> supplierParam) {
        // 注意类型强制转换
        EasyExcelBigSpinnerDataSupplier bean = (EasyExcelBigSpinnerDataSupplier) KnifeSpringUtil.getBean(annotation.supplierBeanClass());
        return bean.easyExcelSpinnerData(supplierParam);
    }

    private String createRefs(Workbook workbook, int currSheetIndex, String[] optionals) {
        // 数据字典的sheet页
        Sheet dictSheet = workbook.createSheet(dictSheetName + currSheetIndex);
        // 从第二个工作簿开始隐藏，为了用户的友好性，将字典sheet隐藏掉
        // 设置隐藏
        workbook.setSheetHidden(currSheetIndex + 1, true);
        int rowLen = optionals.length;
        // 设置字典sheet页的值 每一列一个字典项
        for (int i = 0; i < rowLen; i++) {
            Row row = dictSheet.getRow(i);
            if (row == null) {
                row = dictSheet.createRow(i);
            }
            row.createCell(0).setCellValue(optionals[i]);
        }
        String excelColumn = getExcelColumn(0);
        // 下拉框数据来源 eg:字典sheet!$B1:$B2
        return dictSheetName + currSheetIndex + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
    }

    private void createValidation(WriteSheetHolder writeSheetHolder, Field field, int firstRow, String refers) {
        Sheet currSheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = currSheet.getDataValidationHelper();
        // 获取导出的头字段
        Collection<String> includeColumnFieldNames = writeSheetHolder.getExcelWriteHeadProperty().getHeadMap().values().stream().map(Head::getFieldName).collect(Collectors.toList());

        // 获取需要填充的列
        int tempIndex = -1;
        for (String next : includeColumnFieldNames) {
            if (field.getName().equals(next)) {
                break;
            }
            tempIndex++;
        }
        // 当未匹配到导出的列时，本次不做处理
        if (tempIndex == -1) {
            return;
        }
        // 填充下拉框，"xls", 65536；"xlsx", 1048575
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, 1048575, tempIndex + 1, tempIndex + 1);
        // 设置引用约束
        DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
        // 设置约束
        DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
        // 处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        writeSheetHolder.getSheet().addValidationData(dataValidation);
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (Objects.isNull(templateClass)) {
            return;
        }
        List<Field> spinnerDataAnnotationList = FieldUtils.getFieldsListWithAnnotation(templateClass, BigSpinnerData.class);
        if (CollectionUtils.isNotEmpty(spinnerDataAnnotationList)) {
            // 需要设置下拉框的sheet页
            Sheet currSheet = writeSheetHolder.getSheet();
            Workbook workbook = writeWorkbookHolder.getWorkbook();
            // 获取当前的sheet的编号，创建新的sheet位于当前sheet之后
            int currSheetIndex = workbook.getSheetIndex(currSheet);
            // key：下拉框的字段名，value：有效性检查公式
            Map<String, String> map = new HashMap<>();
            for (Field item : spinnerDataAnnotationList) {
                item.setAccessible(true);
                BigSpinnerData annotation = item.getAnnotation(BigSpinnerData.class);
                String[] optionals = readyBigSpinnerData(annotation, sheetSpinnerSupplierParam);
                String refers = createRefs(workbook, currSheetIndex, optionals);
                createValidation(writeSheetHolder, item, annotation.firstRow(), refers);
                // 处理下一列
                currSheetIndex++;
            }
        }
    }

    /**
     * 将数字列转化成为字母列
     *
     * @param num 数字
     * @return 英文列
     */
    private String getExcelColumn(int num) {
        String column = "";
        int len = alphabet.length - 1;
        int first = num / len;
        int second = num % len;
        if (num <= len) {
            column = alphabet[num] + "";
        } else {
            column = alphabet[first - 1] + "";
            if (second == 0) {
                column = column + alphabet[len] + "";
            } else {
                column = column + alphabet[second - 1] + "";
            }
        }
        return column;

    }
}
